//
//  HMSQLManager.swift
//  02-FMDB演练
//
//  Created by apple on 17/2/13.
//  Copyright © 2017年 apple. All rights reserved.
//

import UIKit
// 数据库名称
private let dbName = "home.db"

class HMSQLManager{
    
    // 全局访问点
    static let shared:HMSQLManager = HMSQLManager()
    
    // 以串行队列方式调度数据库 (代表打开数据库,已经打开 不在关闭)
    var queue: FMDatabaseQueue?
    
    // 实现init
    init() {
        // 路径
        let path = (NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.documentDirectory, FileManager.SearchPathDomainMask.userDomainMask, true).last! as NSString).appendingPathComponent(dbName)
        print(path)
        queue = FMDatabaseQueue(path: path)
        createTable()
    }
    
    // 创建数据库的表
    private func createTable(){
        // 得到文件
        let file = Bundle.main.path(forResource: "db.sql", ofType: nil)!
        
        // 准确sql
        let sql = try! String(contentsOfFile: file)
        // 执行sql
        queue?.inDatabase({ (db) in
            let result = db?.executeStatements(sql)
            if result == false{
                print("创建表失败")
            }else{
                print("创建表成功")
            }
        })
        
        
    }
}

extension HMSQLManager{
    func selectDataWith(sql: String) -> [[String: Any]]{
        // 创建一个临时可变的数组
        var tempArray:[[String: Any]] = [[String: Any]]()
        // 执行sql
        HMSQLManager.shared.queue?.inDatabase({ (db) in
            let resultSet = db?.executeQuery(sql, withArgumentsIn: nil)
            guard let r = resultSet else{
                return
            }
            // 循环获取每行数据
            while r.next() {
                // 创建一个可变的字典
                var dict:[String: Any] = [String: Any]()
                // 遍历列数
                for i in 0..<r.columnCount() {
                    
                    let key = r.columnName(for: i)!
                    let value = r.object(forColumnIndex: i)
                    
                    // 赋值
                    dict[key] = value
                    
                }
                // 保存数据
                tempArray.append(dict)
            }
        })
        
        return tempArray
    }
}








